from typing import List, Optional, Dict, Any
from uuid import UUID
from datetime import datetime
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, update, delete, or_, and_, desc, asc
from sqlalchemy.orm import selectinload

from models.document.personal_document import PersonalDocument
from models.document.document_tag import DocumentTag
from schemas.document.personal_document import PersonalDocumentCreate, PersonalDocumentUpdate


async def get_document_by_id(db: AsyncSession, document_id: UUID) -> Optional[PersonalDocument]:
    """根据ID获取文档详情"""
    
    result = await db.execute(
        select(PersonalDocument)
        .options(
            selectinload(PersonalDocument.category),
            selectinload(PersonalDocument.tags),
            selectinload(PersonalDocument.compliance_relations)
        )
        .where(PersonalDocument.id == document_id)
    )
    return result.scalar_one_or_none()


async def create_document(db: AsyncSession, document: PersonalDocumentCreate, file_info: Dict[str, Any]) -> PersonalDocument:
    """创建新文档"""
    # 创建文档基本信息
    document_data = document.model_dump(exclude={'tags'})
    document_data.update(file_info)
    
    db_document = PersonalDocument(**document_data)
    db.add(db_document)
    await db.flush()  # 获取ID但不提交事务
    
    # 处理标签关联
    if document.tags:
        from crud.document.document_tag import get_or_create_tags_by_names
        from sqlalchemy import text
        
        tags = await get_or_create_tags_by_names(db, document.tags)
        
        # 直接插入关联表记录，避免延迟加载
        for tag in tags:
            await db.execute(
                text("INSERT INTO document_tag_relation (document_id, tag_id) VALUES (:doc_id, :tag_id)"),
                {"doc_id": db_document.id, "tag_id": tag.id}
            )
    
    await db.commit()
    
    # 重新查询文档以预加载关联数据
    result = await db.execute(
        select(PersonalDocument)
        .options(
            selectinload(PersonalDocument.category),
            selectinload(PersonalDocument.tags)
        )
        .where(PersonalDocument.id == db_document.id)
    )
    return result.scalar_one()


async def update_document(db: AsyncSession, document_id: UUID, document: PersonalDocumentUpdate) -> Optional[PersonalDocument]:
    """更新文档信息"""
    db_document = await get_document_by_id(db, document_id)
    if not db_document:
        return None
    
    # 更新基本字段
    update_data = document.model_dump(exclude_unset=True, exclude={'tags'})
    if update_data:
        await db.execute(
            update(PersonalDocument)
            .where(PersonalDocument.id == document_id)
            .values(**update_data)
        )
    
    # 处理标签更新
    if document.tags is not None:
        from crud.document.document_tag import get_or_create_tags_by_names
        from sqlalchemy import text
        
        tags = await get_or_create_tags_by_names(db, document.tags)
        
        # 先删除现有关联关系
        await db.execute(
            text("DELETE FROM document_tag_relation WHERE document_id = :doc_id"),
            {"doc_id": document_id}
        )
        
        # 插入新的关联关系
        for tag in tags:
            await db.execute(
                text("INSERT INTO document_tag_relation (document_id, tag_id) VALUES (:doc_id, :tag_id)"),
                {"doc_id": document_id, "tag_id": tag.id}
            )
    
    await db.commit()
    await db.refresh(db_document)
    return db_document


async def delete_document(db: AsyncSession, document_id: UUID) -> bool:
    """删除文档"""
    result = await db.execute(
        delete(PersonalDocument).where(PersonalDocument.id == document_id)
    )
    await db.commit()
    return result.rowcount > 0


async def get_documents_paginated(
    db: AsyncSession,
    category_id: Optional[UUID] = None,
    search: Optional[str] = None,
    tag_names: Optional[List[str]] = None,
    is_favorite: Optional[bool] = None,
    is_pinned: Optional[bool] = None,
    sort_by: str = "created_at",
    sort_order: str = "desc",
    offset: int = 0,
    limit: int = 20
) -> tuple[List[PersonalDocument], int]:
    """分页获取文档列表"""
    
    # 构建查询
    query = select(PersonalDocument).options(
        selectinload(PersonalDocument.category),
        selectinload(PersonalDocument.tags)
    )
    
    # 构建计数查询
    count_query = select(func.count(PersonalDocument.id))
    
    # 应用筛选条件
    conditions = []
    
    if category_id:
        conditions.append(PersonalDocument.category_id == category_id)
    
    if search:
        search_condition = or_(
            PersonalDocument.title.ilike(f"%{search}%"),
            PersonalDocument.description.ilike(f"%{search}%"),
            PersonalDocument.filename.ilike(f"%{search}%")
        )
        conditions.append(search_condition)
    
    if is_favorite is not None:
        conditions.append(PersonalDocument.is_favorite == is_favorite)
    
    if is_pinned is not None:
        conditions.append(PersonalDocument.is_pinned == is_pinned)
    
    # 标签筛选
    if tag_names:
        query = query.join(PersonalDocument.tags).where(DocumentTag.name.in_(tag_names))
        count_query = count_query.join(PersonalDocument.tags).where(DocumentTag.name.in_(tag_names))
    
    # 应用所有条件
    if conditions:
        query = query.where(and_(*conditions))
        count_query = count_query.where(and_(*conditions))
    
    # 排序
    sort_column = getattr(PersonalDocument, sort_by, PersonalDocument.created_at)
    if sort_order.lower() == "desc":
        query = query.order_by(desc(sort_column))
    else:
        query = query.order_by(asc(sort_column))
    
    # 置顶文档优先
    query = query.order_by(desc(PersonalDocument.is_pinned), desc(sort_column) if sort_order.lower() == "desc" else asc(sort_column))
    
    # 分页
    query = query.offset(offset).limit(limit)
    
    # 执行查询
    documents_result = await db.execute(query)
    documents = documents_result.scalars().all()
    
    total_result = await db.execute(count_query)
    total = total_result.scalar()
    
    return documents, total


async def toggle_favorite(db: AsyncSession, document_id: UUID) -> Optional[PersonalDocument]:
    """切换文档收藏状态"""
    db_document = await get_document_by_id(db, document_id)
    if not db_document:
        return None
    
    await db.execute(
        update(PersonalDocument)
        .where(PersonalDocument.id == document_id)
        .values(is_favorite=not db_document.is_favorite)
    )
    await db.commit()
    
    # 重新查询以获取更新后的数据
    return await get_document_by_id(db, document_id)


async def toggle_pin(db: AsyncSession, document_id: UUID) -> Optional[PersonalDocument]:
    """切换文档置顶状态"""
    db_document = await get_document_by_id(db, document_id)
    if not db_document:
        return None
    
    await db.execute(
        update(PersonalDocument)
        .where(PersonalDocument.id == document_id)
        .values(is_pinned=not db_document.is_pinned)
    )
    await db.commit()
    await db.refresh(db_document)
    return db_document


async def update_view_count(db: AsyncSession, document_id: UUID) -> bool:
    """更新文档查看次数和最后访问时间"""
    result = await db.execute(
        update(PersonalDocument)
        .where(PersonalDocument.id == document_id)
        .values(
            view_count=PersonalDocument.view_count + 1,
            last_accessed_at=datetime.utcnow()
        )
    )
    await db.commit()
    return result.rowcount > 0


async def batch_delete_documents(db: AsyncSession, document_ids: List[UUID]) -> int:
    """批量删除文档"""
    result = await db.execute(
        delete(PersonalDocument).where(PersonalDocument.id.in_(document_ids))
    )
    await db.commit()
    return result.rowcount


async def batch_move_documents(db: AsyncSession, document_ids: List[UUID], category_id: Optional[UUID]) -> int:
    """批量移动文档到指定分类"""
    result = await db.execute(
        update(PersonalDocument)
        .where(PersonalDocument.id.in_(document_ids))
        .values(category_id=category_id)
    )
    await db.commit()
    return result.rowcount


async def get_favorite_documents(db: AsyncSession, limit: int = 10) -> List[PersonalDocument]:
    """获取收藏的文档"""
    result = await db.execute(
        select(PersonalDocument)
        .options(
            selectinload(PersonalDocument.category),
            selectinload(PersonalDocument.tags)
        )
        .where(PersonalDocument.is_favorite == True)
        .order_by(desc(PersonalDocument.updated_at))
        .limit(limit)
    )
    return result.scalars().all()


async def get_recent_documents(db: AsyncSession, limit: int = 10) -> List[PersonalDocument]:
    """获取最近访问的文档"""
    result = await db.execute(
        select(PersonalDocument)
        .options(
            selectinload(PersonalDocument.category),
            selectinload(PersonalDocument.tags)
        )
        .where(PersonalDocument.last_accessed_at.is_not(None))
        .order_by(desc(PersonalDocument.last_accessed_at))
        .limit(limit)
    )
    return result.scalars().all()


async def get_documents_statistics(db: AsyncSession) -> Dict[str, int]:
    """获取文档统计信息"""
    # 总文档数
    total_result = await db.execute(select(func.count(PersonalDocument.id)))
    total_count = total_result.scalar()
    
    # 收藏文档数
    favorite_result = await db.execute(
        select(func.count(PersonalDocument.id)).where(PersonalDocument.is_favorite == True)
    )
    favorite_count = favorite_result.scalar()
    
    # 按文件类型统计
    type_result = await db.execute(
        select(PersonalDocument.file_type, func.count(PersonalDocument.id))
        .group_by(PersonalDocument.file_type)
    )
    type_stats = dict(type_result.all())
    
    return {
        "total": total_count,
        "favorites": favorite_count,
        "by_type": type_stats
    }


async def get_total_file_size(db: AsyncSession) -> int:
    """获取所有文档的总文件大小"""
    result = await db.execute(select(func.sum(PersonalDocument.file_size)))
    total_size = result.scalar()
    return total_size or 0  # 如果没有文档，返回0
